package com.ef.web.action;

import java.io.File;
import java.io.IOException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.Colour;
import jxl.write.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.struts2.ServletActionContext;

import com.ef.domain.Leads;
import com.ef.service.LeadsService;

@SuppressWarnings("deprecation")
public class PteExportAction extends BaseAction<Leads> {

	private static final long serialVersionUID = 1L;

	protected LeadsService leadsService;
 	private String start;
	private String end;
	private Long selMarketingPlanId;
    private Long selCreateUserId;
	public Long getSelCreateUserId() {
		return selCreateUserId;
	}

	public void setSelCreateUserId(Long selCreateUserId) {
		this.selCreateUserId = selCreateUserId;
	}

	public Long getSelMarketingPlanId() {
		return selMarketingPlanId;
	}

	public void setSelMarketingPlanId(Long selMarketingPlanId) {
		this.selMarketingPlanId = selMarketingPlanId;
	}

	public String exportPte() {
		Long teamid=null;
		if (getSessionUser().getTeam() != null) {
			if (getSessionUser().getTeam().getName().toUpperCase()
					.indexOf("MK") > -1) {
				teamid = getSessionUser().getTeam().getId();
			}
		}
		List<Object> result = leadsService.exportPteResults(start, end,
				selMarketingPlanId,selCreateUserId,teamid);
		HttpServletRequest request = getRequest();
		HttpServletResponse response = getResponse();
		response.setContentType("application/ms-excel;charset=UTF-8");
		response.setHeader("Content-Disposition",
				"attachment;filename=exportPTE.xls");
		File tempPath = new File(ServletActionContext.getServletContext()
				.getRealPath("/temp"));
		if (!tempPath.exists()) {
			tempPath.mkdirs();
		}
		WritableWorkbook workBook;

		File exportFile = new File(ServletActionContext.getServletContext()
				.getRealPath("/temp/exportPTE.xls"));
		try {

			WritableFont font = new WritableFont(WritableFont.TIMES, 12,
					WritableFont.BOLD);
			WritableCellFormat headStyle = new WritableCellFormat(font);
			headStyle.setAlignment(Alignment.CENTRE);
			WritableCellFormat titleStyle = new WritableCellFormat(font);
			titleStyle.setAlignment(Alignment.CENTRE);
			titleStyle.setWrap(false);
			titleStyle.setBorder(Border.ALL, BorderLineStyle.THIN);
			WritableFont font_content = new WritableFont(WritableFont.TIMES, 10);
			WritableCellFormat contentStyle = new WritableCellFormat(
					font_content);
			contentStyle.setAlignment(Alignment.CENTRE);
			contentStyle.setBorder(Border.ALL, BorderLineStyle.THIN);
			contentStyle.setWrap(false);
			workBook = Workbook.createWorkbook(exportFile);
			workBook.setProtected(false);
			WritableCellFormat hzStyle = new WritableCellFormat(font_content);
			hzStyle.setBorder(Border.ALL, BorderLineStyle.THIN);
			hzStyle.setAlignment(Alignment.CENTRE);
			hzStyle.setBackground(Colour.GREY_25_PERCENT);
			// create sheets
			WritableSheet activeSheet = workBook.createSheet("Export Leads", 0);

			activeSheet.addCell(new Label(0, 0, "PTE ", titleStyle));
			activeSheet.addCell(new Label(1, 0, "PNO", titleStyle));
			activeSheet.addCell(new Label(2, 0, "TeamLeader", titleStyle));
			activeSheet.addCell(new Label(3, 0, "TNO", titleStyle));
			activeSheet.addCell(new Label(4, 0, "MarketPlan", titleStyle));
			activeSheet.addCell(new Label(5, 0, "Leads", titleStyle));
			activeSheet.addCell(new Label(6, 0, "Stauts", titleStyle));
			activeSheet.addCell(new Label(7, 0, "Invalid", titleStyle));
			activeSheet.addCell(new Label(8, 0, "Valid", titleStyle));
			activeSheet.addCell(new Label(9, 0, "Noanswer", titleStyle));
			activeSheet.addCell(new Label(10, 0, "Showup", titleStyle));
			activeSheet.addCell(new Label(11, 0, "Pay", titleStyle));
			activeSheet.addCell(new Label(12, 0, "Creator", titleStyle));
			activeSheet.addCell(new Label(13, 0, "CreateDate", titleStyle));
			activeSheet.addCell(new Label(14, 0, "TM", titleStyle));
			int preTotal = 0;
			int preInvalid = 0;
			int preValid = 0;
			int preNoanswer = 0;
			int preShowup = 0;
			int prePay = 0;
			int total = 0;
			int invalid = 0;
			int valid = 0;
			int noanswer = 0;
			int showup = 0;
			int pay = 0;
			String prevId = null;
			int i = 0;
			int c;
			for (c = 0; c < result.size(); c++) {
				Object[] objList = (Object[]) result.get(c);
				if (prevId == null) {
					prevId = (String) objList[0];
				}

				if (!prevId.equals((String) objList[0])) {
					activeSheet.addCell(new Label(0, i + 1, prevId + "汇总",
							hzStyle));
					activeSheet.addCell(new Label(1, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(2, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(3, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(4, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(5, i + 1, String
							.valueOf(preTotal), hzStyle));
					activeSheet.addCell(new Label(6, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(7, i + 1, String
							.valueOf(preInvalid), hzStyle));
					activeSheet.addCell(new Label(8, i + 1, String
							.valueOf(preValid), hzStyle));
					activeSheet.addCell(new Label(9, i + 1, String
							.valueOf(preNoanswer), hzStyle));
					activeSheet.addCell(new Label(10, i + 1, String
							.valueOf(preShowup), hzStyle));
					activeSheet.addCell(new Label(11, i + 1, String
							.valueOf(prePay), hzStyle));
					preTotal = 0;
					preInvalid = 0;
					preValid = 0;
					preNoanswer = 0;
					preShowup = 0;
					prePay = 0;
					i++;
				}
				prevId = (String) objList[0];
				activeSheet.addCell(new Label(0, i + 1, objList[0] == null ? ""
						: objList[0].toString(), contentStyle));
				activeSheet.addCell(new Label(1, i + 1, objList[1] == null ? ""
						: objList[1].toString(), contentStyle));
				activeSheet.addCell(new Label(2, i + 1, objList[2] == null ? ""
						: objList[2].toString(), contentStyle));
				activeSheet.addCell(new Label(3, i + 1, objList[3] == null ? ""
						: objList[3].toString(), contentStyle));
				activeSheet.addCell(new Label(4, i + 1, objList[4] == null ? ""
						: objList[4].toString(), contentStyle));
				activeSheet.addCell(new Label(5, i + 1, objList[5] == null ? ""
						: objList[5].toString(), contentStyle));
				activeSheet.addCell(new Label(6, i + 1, objList[6] == null ? ""
						: objList[6].toString(), contentStyle));
				activeSheet.addCell(new Label(7, i + 1, objList[7] == null ? ""
						: objList[7].toString(), contentStyle));
				activeSheet.addCell(new Label(8, i + 1, objList[8] == null ? ""
						: objList[8].toString(), contentStyle));
				activeSheet.addCell(new Label(9, i + 1, objList[9] == null ? ""
						: objList[9].toString(), contentStyle));
				activeSheet.addCell(new Label(10, i + 1,
						objList[10] == null ? "" : objList[10].toString(),
						contentStyle));
				activeSheet.addCell(new Label(11, i + 1,
						objList[11] == null ? "" : objList[11].toString(),
						contentStyle));
				activeSheet.addCell(new Label(12, i + 1,
						objList[12] == null ? "" : objList[12].toString(),
						contentStyle));
				activeSheet.addCell(new Label(13, i + 1,
						objList[13] == null ? "" : objList[13].toString(),
						contentStyle));
				activeSheet.addCell(new Label(14, i + 1,
						objList[14] == null ? "" : objList[14].toString(),
						contentStyle));
				preTotal++;
				total++;
				if (objList[7].equals("1")) {
					preInvalid++;
					invalid++;

				}
				if (objList[8].equals("1")) {
					preValid++;
					valid++;

				}
				if (objList[9].equals("1")) {
					preNoanswer++;
					noanswer++;

				}
				if (objList[10].equals("1")) {
					preShowup++;
					showup++;

				}
				if (objList[11].equals("1")) {
					prePay++;
					pay++;

				}
				i++;
			}

			if (result.size() > 0) {
				activeSheet
						.addCell(new Label(0, i + 1, prevId + "汇总", hzStyle));
				activeSheet.addCell(new Label(1, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(2, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(3, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(4, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(5, i + 1, String
						.valueOf(preTotal), hzStyle));
				activeSheet.addCell(new Label(6, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(7, i + 1, String
						.valueOf(preInvalid), hzStyle));
				activeSheet.addCell(new Label(8, i + 1, String
						.valueOf(preValid), hzStyle));
				activeSheet.addCell(new Label(9, i + 1, String
						.valueOf(preNoanswer), hzStyle));
				activeSheet.addCell(new Label(10, i + 1, String
						.valueOf(preShowup), hzStyle));
				activeSheet.addCell(new Label(11, i + 1,
						String.valueOf(prePay), hzStyle));
				i += 2;

				activeSheet.addCell(new Label(1, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(2, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(3, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(4, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(5, i + 1, String.valueOf(total),
						hzStyle));
				activeSheet.addCell(new Label(6, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(7, i + 1,
						String.valueOf(invalid), hzStyle));
				activeSheet.addCell(new Label(8, i + 1, String.valueOf(valid),
						hzStyle));
				activeSheet.addCell(new Label(9, i + 1, String
						.valueOf(noanswer), hzStyle));
				activeSheet.addCell(new Label(10, i + 1,
						String.valueOf(showup), hzStyle));
				activeSheet.addCell(new Label(11, i + 1, String.valueOf(pay),
						hzStyle));
			}
			workBook.write();
			workBook.close();

		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
		RequestDispatcher dispatcher = request
				.getRequestDispatcher("../../temp/exportPTE.xls");
		try {
			dispatcher.forward(request, response);
		} catch (ServletException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return NONE;
	}
	public String exportPteLeader() {
		Long teamid=null;
		if (getSessionUser().getTeam() != null) {
			if (getSessionUser().getTeam().getName().toUpperCase()
					.indexOf("MK") > -1) {
				teamid = getSessionUser().getTeam().getId();
			}
		}
		List<Object> result = leadsService.exportPteLeadResults(start, end,
				selMarketingPlanId,this.selCreateUserId,teamid);
		HttpServletRequest request = getRequest();
		HttpServletResponse response = getResponse();
		response.setContentType("application/ms-excel;charset=UTF-8");
		response.setHeader("Content-Disposition",
				"attachment;filename=exportPTELeader.xls");
		File tempPath = new File(ServletActionContext.getServletContext()
				.getRealPath("/temp"));
		if (!tempPath.exists()) {
			tempPath.mkdirs();
		}
		WritableWorkbook workBook;

		File exportFile = new File(ServletActionContext.getServletContext()
				.getRealPath("/temp/exportPTELeader.xls"));
		try {

			WritableFont font = new WritableFont(WritableFont.TIMES, 12,
					WritableFont.BOLD);
			WritableCellFormat headStyle = new WritableCellFormat(font);
			headStyle.setAlignment(Alignment.CENTRE);
			WritableCellFormat titleStyle = new WritableCellFormat(font);
			titleStyle.setAlignment(Alignment.CENTRE);
			titleStyle.setWrap(false);
			titleStyle.setBorder(Border.ALL, BorderLineStyle.THIN);
			WritableFont font_content = new WritableFont(WritableFont.TIMES, 10);
			WritableCellFormat contentStyle = new WritableCellFormat(
					font_content);
			contentStyle.setAlignment(Alignment.CENTRE);
			contentStyle.setBorder(Border.ALL, BorderLineStyle.THIN);
			contentStyle.setWrap(false);
			workBook = Workbook.createWorkbook(exportFile);
			workBook.setProtected(false);
			WritableCellFormat hzStyle = new WritableCellFormat(font_content);
			hzStyle.setBorder(Border.ALL, BorderLineStyle.THIN);
			hzStyle.setAlignment(Alignment.CENTRE);
			hzStyle.setBackground(Colour.GREY_25_PERCENT);
			// create sheets
			WritableSheet activeSheet = workBook.createSheet("Export Leads", 0);

			activeSheet.addCell(new Label(0, 0, "TeamLeader", titleStyle));
			activeSheet.addCell(new Label(1, 0, "TNO", titleStyle));
			activeSheet.addCell(new Label(2, 0, "PTE", titleStyle));
			activeSheet.addCell(new Label(3, 0, "PNO", titleStyle));
			activeSheet.addCell(new Label(4, 0, "MarketPlan", titleStyle));
			activeSheet.addCell(new Label(5, 0, "Leads", titleStyle));
			activeSheet.addCell(new Label(6, 0, "Stauts", titleStyle));
			activeSheet.addCell(new Label(7, 0, "Invalid", titleStyle));
			activeSheet.addCell(new Label(8, 0, "Valid", titleStyle));
			activeSheet.addCell(new Label(9, 0, "Noanswer", titleStyle));
			activeSheet.addCell(new Label(10, 0, "Showup", titleStyle));
			activeSheet.addCell(new Label(11, 0, "Pay", titleStyle));
			activeSheet.addCell(new Label(12, 0, "Creator", titleStyle));
			activeSheet.addCell(new Label(13, 0, "CreateDate", titleStyle));
			activeSheet.addCell(new Label(14, 0, "TM", titleStyle));
			int preTotal = 0;
			int preInvalid = 0;
			int preValid = 0;
			int preNoanswer = 0;
			int preShowup = 0;
			int prePay = 0;
			int total = 0;
			int invalid = 0;
			int valid = 0;
			int noanswer = 0;
			int showup = 0;
			int pay = 0;
			String prevId = null;
			int i = 0;
			int c;
			for (c = 0; c < result.size(); c++) {
				Object[] objList = (Object[]) result.get(c);
				if (prevId == null) {
					prevId = (String) objList[0];
				}

				if (!prevId.equals((String) objList[0])) {
					activeSheet.addCell(new Label(0, i + 1, prevId + "汇总",
							hzStyle));
					activeSheet.addCell(new Label(1, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(2, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(3, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(4, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(5, i + 1, String
							.valueOf(preTotal), hzStyle));
					activeSheet.addCell(new Label(6, i + 1, "", hzStyle));
					activeSheet.addCell(new Label(7, i + 1, String
							.valueOf(preInvalid), hzStyle));
					activeSheet.addCell(new Label(8, i + 1, String
							.valueOf(preValid), hzStyle));
					activeSheet.addCell(new Label(9, i + 1, String
							.valueOf(preNoanswer), hzStyle));
					activeSheet.addCell(new Label(10, i + 1, String
							.valueOf(preShowup), hzStyle));
					activeSheet.addCell(new Label(11, i + 1, String
							.valueOf(prePay), hzStyle));
					preTotal = 0;
					preInvalid = 0;
					preValid = 0;
					preNoanswer = 0;
					preShowup = 0;
					prePay = 0;
					i++;
				}
				prevId = (String) objList[0];
				activeSheet.addCell(new Label(0, i + 1, objList[0] == null ? ""
						: objList[0].toString(), contentStyle));
				activeSheet.addCell(new Label(1, i + 1, objList[1] == null ? ""
						: objList[1].toString(), contentStyle));
				activeSheet.addCell(new Label(2, i + 1, objList[2] == null ? ""
						: objList[2].toString(), contentStyle));
				activeSheet.addCell(new Label(3, i + 1, objList[3] == null ? ""
						: objList[3].toString(), contentStyle));
				activeSheet.addCell(new Label(4, i + 1, objList[4] == null ? ""
						: objList[4].toString(), contentStyle));
				activeSheet.addCell(new Label(5, i + 1, objList[5] == null ? ""
						: objList[5].toString(), contentStyle));
				activeSheet.addCell(new Label(6, i + 1, objList[6] == null ? ""
						: objList[6].toString(), contentStyle));
				activeSheet.addCell(new Label(7, i + 1, objList[7] == null ? ""
						: objList[7].toString(), contentStyle));
				activeSheet.addCell(new Label(8, i + 1, objList[8] == null ? ""
						: objList[8].toString(), contentStyle));
				activeSheet.addCell(new Label(9, i + 1, objList[9] == null ? ""
						: objList[9].toString(), contentStyle));
				activeSheet.addCell(new Label(10, i + 1,
						objList[10] == null ? "" : objList[10].toString(),
						contentStyle));
				activeSheet.addCell(new Label(11, i + 1,
						objList[11] == null ? "" : objList[11].toString(),
						contentStyle));
				activeSheet.addCell(new Label(12, i + 1,
						objList[12] == null ? "" : objList[12].toString(),
						contentStyle));
				activeSheet.addCell(new Label(13, i + 1,
						objList[13] == null ? "" : objList[13].toString(),
						contentStyle));
				activeSheet.addCell(new Label(14, i + 1,
						objList[14] == null ? "" : objList[14].toString(),
						contentStyle));
				preTotal++;
				total++;
				if (objList[7].equals("1")) {
					preInvalid++;
					invalid++;

				}
				if (objList[8].equals("1")) {
					preValid++;
					valid++;

				}
				if (objList[9].equals("1")) {
					preNoanswer++;
					noanswer++;

				}
				if (objList[10].equals("1")) {
					preShowup++;
					showup++;

				}
				if (objList[11].equals("1")) {
					prePay++;
					pay++;

				}
				i++;
			}

			if (result.size() > 0) {
				activeSheet
						.addCell(new Label(0, i + 1, prevId + "汇总", hzStyle));
				activeSheet.addCell(new Label(1, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(2, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(3, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(4, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(5, i + 1, String
						.valueOf(preTotal), hzStyle));
				activeSheet.addCell(new Label(6, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(7, i + 1, String
						.valueOf(preInvalid), hzStyle));
				activeSheet.addCell(new Label(8, i + 1, String
						.valueOf(preValid), hzStyle));
				activeSheet.addCell(new Label(9, i + 1, String
						.valueOf(preNoanswer), hzStyle));
				activeSheet.addCell(new Label(10, i + 1, String
						.valueOf(preShowup), hzStyle));
				activeSheet.addCell(new Label(11, i + 1,
						String.valueOf(prePay), hzStyle));
				i += 2;

				activeSheet.addCell(new Label(1, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(2, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(3, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(4, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(5, i + 1, String.valueOf(total),
						hzStyle));
				activeSheet.addCell(new Label(6, i + 1, "", hzStyle));
				activeSheet.addCell(new Label(7, i + 1,
						String.valueOf(invalid), hzStyle));
				activeSheet.addCell(new Label(8, i + 1, String.valueOf(valid),
						hzStyle));
				activeSheet.addCell(new Label(9, i + 1, String
						.valueOf(noanswer), hzStyle));
				activeSheet.addCell(new Label(10, i + 1,
						String.valueOf(showup), hzStyle));
				activeSheet.addCell(new Label(11, i + 1, String.valueOf(pay),
						hzStyle));
			}
			workBook.write();
			workBook.close();

		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
		RequestDispatcher dispatcher = request
				.getRequestDispatcher("../../temp/exportPTELeader.xls");
		try {
			dispatcher.forward(request, response);
		} catch (ServletException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return NONE;
	}
	protected void fetchListData() {

	}

	public String list() {
		fetchListData();
		return LIST;
	}

	public LeadsService getLeadsService() {
		return leadsService;
	}

	public void setLeadsService(LeadsService leadsService) {
		this.leadsService = leadsService;
	}

	public String getStart() {
		return start;
	}

	public void setStart(String start) {
		this.start = start;
	}

	public String getEnd() {
		return end;
	}

	public void setEnd(String end) {
		this.end = end;
	}
}
